from osv import osv, fields
from osv.orm import intersect
    
class kdvn_contract_client(osv.osv):
    _name = "kdvn.contract.client"
    _description="KDVN Contract Client"
    
    def fields_get(self, cr, uid, fields=None, context=None):
        res = super(kdvn_contract_client, self).fields_get(cr, uid, fields, context)
        new_field={'search_in_line_project':
                                        {'widget':u'char',
                                         'type':u'char',
                                         'name':u'search_in_line_project',
                                         'select': u'1',
                                         'string':u'Project',
                                         'invisible':True,
                                         'visible':False,
                                         'size':128,
                                         'sql':"(Select distinct so.contract_id from sale_order so left join kdvn_project kp on so.project_e_id = kp.id or so.project_e_id = kp.id where coalesce(so.contract_id,0)<>0 and kp.name || ' ' || kp.code ilike chr(37) || '%s' || chr(37))"
                                         }
                   }
        res.update(new_field)
        return res

    def copy(self, cr, uid, id, default=None,context={}):
        raise osv.except_osv("KDVN Error","Can't Duplicate this contract")
        return super(kdvn_contract_client, self).copy(cr, uid, id, default, context)

    def _create_payment_from_client(self,cr, uid, ids, context):
        a=[]
        term_ids=[]
        if context:
            if 'term_ids' in context:
                term_ids = context['term_ids']
        str_term_ids = ",".join(map(str,term_ids))
        for ctc in self.browse(cr, uid, ids, context):
            if ctc.kderp_contract_payment_term_line:                
                pfc_obj=self.pool.get('kdvn.payment.from.client')
                #raise osv.except_osv("E",pfc_obj.search(cr,uid, [('contract_id', '=', ctc.id),('payment_term_line_id','in',term_ids)]))
                if pfc_obj.search(cr,uid, [('contract_id', '=', ctc.id),('payment_term_line_id','in',term_ids)]):
                    raise osv.except_osv("Error","Payment already exist !")
                else:
                    #kdvn_payment_term_line_ids=self.pool.get('kderp.contract.payment.term.line').search(cr,uid,[('contract_id','=',ctc.id)])
                    #kdvn_payments=self.pool.get('kderp.contract.payment.term.line').read(cr,uid,kdvn_payment_term_line_ids,['value_amount','sequence','name','type','id'])
                    
                    result={}
                    
                    #Tax Per
                    tax_per = ctc.taxes_percent or 0
                    wh_per = ctc.with_holding_taxes_percent or 0
                    payment_lists = []
                    
                    result['number'] = False
                    
                    if ctc.client_id:
                        result['client_id'] = ctc.client_id.id
                    else:
                        result['client_id'] = False
                        
                    if ctc.owner_id:
                        result['owner_id'] = ctc.owner_id.id
                    else:
                        result['owner_id'] = False
                        
                    if ctc.address_id:
                        result['address_id'] = ctc.address_id.id
                    else:
                        result['address_id'] = False
                        
                    if ctc.invoice_address_id:
                        result['invoice_address_id'] = ctc.invoice_address_id.id
                    else:
                        result['invoice_address_id'] = False

                    result['contract_id']=ctc.id
                       # result['contract_currency_id']=ctc.contract_currency.id
                    jobs_detail = {}
                    for curr_line in ctc.currency_line:
                        curr_id = curr_line.currency_id.id
                        curr_name = curr_line.currency_id.code
                        #raise osv.except_osv("E",(curr_name))
                        payment_detail = []
                        ctc_total_amount = 0.0
                        for job_obj in ctc.project_estimated_ids:
                            ctc_total_amount = ctc_total_amount + job_obj[curr_name.lower()]
                            jobs_detail.update({job_obj.project_id.id:{curr_name:{'amount':job_obj[curr_name.lower()],'adv_amount':0.0,'re_amount':0.0}}})
                       # ctc_total_amount = ctc_amount_e + ctc_amount_m
                        ctc_amount_vat = curr_line.vat_amount or 0.0
                        ctc_amount_wh_tax = curr_line.wht_amount
                        
                        #kdvn_payments.sort(key=lambda x:x['sequence']) #Theo ID                
                        
                        #result=pfc_obj._onchage_contract_id(cr,uid,ids,ctc.id)
                        result['currency_id'] = curr_id
                        result['taxes_percent']= tax_per
                        result['with_holding_taxes_percent']= wh_per
                        result['payment_details_ids'] = payment_detail
                        adv = 0.0
                        retention = 0.0
                        progress_amount = 0.0
                        vat_tax_amount = 0.0
                        ctc_amount_wh_tax = 0.0
                        
                        tmp_adv = 0
                        tmp_retention = 0
                        tmp_progress = 0
                        
                        if ctc_total_amount>0:
                            for pml in ctc.kderp_contract_payment_term_line:
                                if pml.term_id.type=='adv':
                                    for job_obj in jobs_detail:
                                        jobs_detail[job_obj][curr_name].update({'adv_amount':jobs_detail[job_obj][curr_name]['adv_amount']+jobs_detail[job_obj][curr_name]['amount']*pml.value_amount / 100.0})
                                    result['progress_amount'] =  0.0
                                    per_in_text = str(pml.value_amount) + "% "
                                    if pml.term_id.name.find("%")<0:
                                        result['itemofrequest'] = pml.term_id.name + " " + per_in_text +  (ctc.project_name or "")
                                    else:
                                        result['itemofrequest'] = pml.term_id.name + " " +  (ctc.project_name or "")
                                    
                                    result['name'] = ctc.description
                                    result['payment_term_line_id'] = pml.id
                                    
                                    cal_amount =round(ctc_total_amount * pml.value_amount / 100.0,2) #Advanced
                                    result['adv_payment'] = cal_amount                                                    
                                    result['retention'] = 0.0
                                    result['kdvn_amount_tax']= round(cal_amount*tax_per / 100.0,2)
                                    result['with_holding_amount']= round(cal_amount*wh_per / 100.0,2)
                                    tmp_adv = tmp_adv + cal_amount
                                    
                                    payment_detail = []
                                    if pml.id in term_ids:
                                        for job_obj in ctc.project_estimated_ids:
                                            payment_detail.append((0,0,{'project_id':job_obj.project_id.id,'amount':job_obj[curr_name.lower()]*pml.value_amount / 100.0}))
                                        result['payment_details_ids'] = payment_detail
                                    
                                        payment_lists.append(result.copy())
                                    
                                elif pml.term_id.type =='re':
                                    result['progress_amount'] =  0.0
                                    
                                    per_in_text = str(pml.value_amount) + "% "
                                    if pml.term_id.name.find("%")<0:
                                        result['itemofrequest'] = pml.term_id.name + " " + per_in_text +  (ctc.project_name or "")
                                    else:
                                        result['itemofrequest'] = pml.term_id.name + " " +  (ctc.project_name or "")
                                    
                                    #result['itemofrequest'] = pml.term_id.name  + " " +  ctc.project_name
                                    result['name'] = ctc.description
                                    result['payment_term_line_id'] = pml.id
                                    for job_obj in jobs_detail:
                                        jobs_detail[job_obj][curr_name].update({'re_amount':jobs_detail[job_obj][curr_name]['re_amount']+jobs_detail[job_obj][curr_name]['amount']*pml.value_amount / 100.0})                                    
                                    cal_amount =round( ctc_total_amount * pml.value_amount / 100.0,2) #Retention
                                    result['kdvn_amount_tax']= round(cal_amount*tax_per/100.0,2) #VAT Tax 
                                    result['with_holding_amount']= round(cal_amount*wh_per/100.0,2)
                                    result['retention'] = cal_amount
                                    result['adv_payment'] = 0.0
                                    tmp_retention = tmp_retention + cal_amount
                                    
                                    payment_detail = []
                                    if pml.id in term_ids:
                                        for job_obj in ctc.project_estimated_ids:
                                            payment_detail.append((0,0,{'project_id':job_obj.project_id.id,'amount':job_obj[curr_name.lower()]*pml.value_amount / 100.0}))
                                        result['payment_details_ids'] = payment_detail
                                        
                                        payment_lists.append(result.copy())
                            #raise osv.except_osv("E",jobs_detail)
                            for pml in ctc.kderp_contract_payment_term_line:                                
                                if pml.id in term_ids and pml.term_id.type not in ['adv','re']:
                                    result['progress_amount'] =  0.0
                                    
                                    per_in_text = str(pml.value_amount) + "% "
                                    if pml.term_id.name.find("%")<0:
                                        result['itemofrequest'] = pml.term_id.name + " " + per_in_text +  (ctc.project_name or "")
                                    else:
                                        result['itemofrequest'] = pml.term_id.name + " " +  (ctc.project_name or "")
                                    #result['itemofrequest'] = pml.term_id.name  + " " +  ctc.project_name
                                    
                                    result['name'] = ctc.description
                                    result['payment_term_line_id'] = pml.id
                                    
                                    result['adv_payment']=0.0
                                    result['retention']=0.0
                                    
                                    cal_amount = round(ctc_total_amount * pml.value_amount / 100.0,2) #Retention
                                    x_adv_payment = round(- tmp_adv * pml.value_amount / 100.0,2)
                                    x_retention = round(- tmp_retention * pml.value_amount / 100.0,2)
                                    claim_amount = cal_amount + x_adv_payment + x_retention
                                    
                                    result['kdvn_amount_tax']= round(claim_amount * tax_per/100.0,2) #VAT Tax 
                                    result['with_holding_amount']= round(claim_amount * wh_per/100.0,2)
                                    result['retention'] = x_retention
                                    result['adv_payment'] = x_adv_payment
                                    result['progress_amount'] = cal_amount
                                    
                                    payment_detail = []
                                    for job_obj in ctc.project_estimated_ids:
                                        total_job_amount = job_obj[curr_name.lower()]
                                        job_cal_amount = round(total_job_amount * pml.value_amount / 100.0 ,2)
                                        job_x_adv_payment =round( - jobs_detail[job_obj.project_id.id][curr_name]['adv_amount'] * pml.value_amount / 100.0,2)
                                        job_x_retention = round(- jobs_detail[job_obj.project_id.id][curr_name]['re_amount'] * pml.value_amount / 100.0,2)
                                        
                                        job_amount = job_cal_amount + job_x_adv_payment + job_x_retention
                                        payment_detail.append((0,0,{'project_id':job_obj.project_id.id,'amount':job_amount}))
                                        
                                    result['payment_details_ids'] = payment_detail
                                        
                                    payment_lists.append(result.copy())

                    for pmt in payment_lists: 
                        invoice_id=pfc_obj.create(cr,uid,pmt)
            else:
                raise osv.except_osv("Error","Please check payment term for this job !")
        return True
        
    def name_search(self, cr, user, name='', args=None, operator='ilike', context=None, limit=80):
        if not args:
            args=[]
        if not context:
            context={}
        ids = self.search(cr, user, [('contract_no','=',name)]+ args, limit=limit, context=context)
        if not len(ids):
            ids += self.search(cr, user, [('contract_no','ilike',name)]+ args, limit=limit, context=context)
        if not len(ids):
            ids += self.search(cr, user, [('description','ilike',name)]+ args, limit=limit, context=context)
        return self.name_get(cr, user, ids, context=context)
    
    def _get_quotation_lists(self, cr, uid, ids, name, arg, context=None):
        res = {}
        for id in ids:
            cr.execute('SELECT id FROM sale_order ' \
                    'WHERE contract_id =%s' % (id))
            res[id] = [x[0] for x in cr.fetchall()]
        return res
    
    def name_get(self, cr, uid, ids, context={}):
        if not len(ids):
            return []
        reads = self.read(cr, uid, ids, ['contract_no', 'description'], context)
        res = []
        for record in reads:
            name = record['description']
            if record['contract_no'] and name:
                name = record['contract_no'] + ' - ' + name
            res.append((record['id'], name))
        return res
    
    def _get_contract_currency(self,cr,uid,ids,name,args,context={}):
        res={}
        if ids:
            acc_set=",".join(map(str,ids))
            cr.execute("Select contract_id,id from project_currency where default_curr=True and contract_id in (%s)" % acc_set)
            for ctr_id,curr_id in cr.fetchall():
                res[ctr_id]=curr_id#,'contract_rate':curr_rate}
        for id in ids:
            res[id]=res.get(id,False)            
        #raise osv.except_osv("E",res)
        return res
    
    def _kdvn_contract_info(self, cr, uid, ids, name, args, context={}):
        res = {}
        contract_amounte=0.0
        contract_amountm=0.0
        contract_vat=0.0
        val_temp={'contract_amount_e':0,'contract_amount_m':0,
                     'contract_vat':0,'contract_total':0,
                     'owner_id':False,'client_id':False,
                     'address_id':False,'invoice_address_id':False}
        
        address_id = False
        client_id = False
        owner_id = False
        invoice_address_id = False
        
        for x in self.browse(cr,uid,ids, context=context):
            for prj in x.project_ids:
                if not owner_id:
                    owner_id = prj.owner_id.id
                if not client_id:
                    client_id = prj.client_id.id
                if not address_id:
                    address_id = prj.address_id.id
                if not invoice_address_id:
                    invoice_address_id = prj.invoice_address_id.id
                if prj.project_type=='E':
                    contract_amounte = contract_amounte + prj.project_amount
                    contract_vat = contract_vat + prj.project_vat
                else:
                    contract_amountm = contract_amountm + prj.project_amount
                    contract_vat = contract_vat + prj.project_vat
            val={'contract_amount_e':contract_amounte,'contract_amount_m':contract_amountm,
                     'contract_vat':contract_vat,'contract_total':contract_amounte+contract_amountm+contract_vat,
                     'owner_id':owner_id,'client_id':client_id,
                     'address_id':address_id,'invoice_address_id':invoice_address_id}
            res[x.id]=val
        return res
  
    def _get_quotation_info(self, cr, uid, ids, name, args, context=None):
        #tmp={'quotations':False, 'scopeofwork':False}
        res={}
        if ids:
            for id in ids:
                
                cr.execute("Select \
                                name,\
                                trim(description)\
                            from \
                                sale_order so \
                            where\
                                contract_id=%s \
                                and state not in ('draft','cancel') order by name" % (id))
                quotations=False
                #scopeofwork=False
                for qno,scope in cr.fetchall():
                    if quotations:
                        if qno:
                            quotations=quotations+"; "+qno
                    else:
                        quotations=qno

                res[id]=quotations
        return res
    
    def _newcode_suggest(self,cr,uid,context={}):
        selection=[]
        cr.execute("Select newcode,newcode || ': ' || name  from vwnewcode_contract order by newcode asc")
        list_newcode=cr.fetchall()
        return list_newcode

    def _get_payment_term(self, cr, uid, ids, name, args, context=None):
        res={}
        if ids:
            for id in ids:
                res[id] = False
            ctc_ids = ','.join(map(str,ids))
            cr.execute("""Select 
                            id,
                            sum(name || ', ')
                        from
                            (Select 
                                kcc.id,
                                kptc.name || case when coalesce(kptc.value_amount,0)=0 then to_char(pt.value_amount,' FM999%%') else '' end as name
                            from 
                                kdvn_contract_client kcc
                            left join
                                kderp_contract_payment_term_line pt on kcc.id = pt.contract_id
                            left join
                                kderp_payment_term_code kptc on pt.term_id = kptc.id
                            where
                                kcc.id in (%s)
                            order by 
                                kcc.id,
                                pt.sequence) vwterm
                        group by id""" % (ctc_ids))
            for id,name in cr.fetchall():
                res[id] = name
        return res
        
    def _get_tax_percents(self,cr,uid,context={}):
        selection = []
        cr.execute("Select name,tax_percent from kderp_tax_code where active=true order by tax_percent asc")
        for name,per in cr.fetchall():
            selection.append((per,name))
        return selection

#    def write(self, cr, uid, ids, vals, context=None):
#        osv.osv.write(self, cr, uid, ids,vals,context=context)
#        #raise osv.except_osv('e',vals)
#        return True 
    def _get_payment_terms(self, cr, uid, ids, name1, args, context={}):
        res = {}
        if ids:
            ctc_ids = ",".join(map(str,ids))
            tmp = {'count_down_payment':0,
                   'down_payment_term':'',
                   
                   'progress_payment_term':'',
                   'count_progress_payment':0,
                   
                   'retention_payment_term':'',
                   'count_retention_payment':0,
                   
                   'after_compltetion_payment_term':'',
                   'count_after_compltetion_payment':0,
                   
                   'schedule_payment_term':'',
                   'count_schedule_payment':0}
            for id in ids:
                res[id]=tmp
            cr.execute("""Select
                            kcc.id,
                            type,
                            kptc.name,
                            kcptl.value_amount,
                            sequence
                        from 
                            kdvn_contract_client kcc
                        left join
                            kderp_contract_payment_term_line kcptl on kcc.id = kcptl.contract_id
                        left join
                            kderp_payment_term_code kptc on kcptl.term_id = kptc.id
                        where
                            coalesce(type,'')<>'' and type<>'p' and kcc.id in (%s)
                        Union
                        Select
                            kcc.id,
                            type,
                            'Progress ' as name,
                            sum(kcptl.value_amount) - coalesce((Select sum(kcptl_sub.value_amount) from kderp_contract_payment_term_line kcptl_sub left join
                                                            kderp_payment_term_code kptc_sub on kcptl_sub.term_id = kptc_sub.id
                                                        where contract_id=kcc.id and (type='adv' or type='re')),0) as value_amount,
                            0 as sequence                                
                        from 
                            kdvn_contract_client kcc
                        left join
                            kderp_contract_payment_term_line kcptl on kcc.id = kcptl.contract_id
                        left join
                            kderp_payment_term_code kptc on kcptl.term_id = kptc.id
                        where
                            coalesce(type,'')<>'' and type='p' and kcc.id in (%s)
                        group by
                            kcc.id,type""" % (ctc_ids,ctc_ids))
            list_number_t = {1:'%s%% I',2:'\n%s%% II',
                           3:'\n%s%% III',4:'\n%s%% IV',5:'\n%s%% V',
                           6:'\n%s%% V',7:'\n%s%% VII',8:'\n%s%% VII',
                           9:'\n%s%% IX'}
            list_number = {1:'%s%%',2:'\n%s%%',
                           3:'\n%s%%',4:'\n%s%%',5:'\n%s%%',
                           6:'\n%s%%',7:'\n%s%%',8:'\n%s%%',
                           9:'\n%s%%'}
            down_payment = 'down_payment_term'
            count_down_payment = 0
            
            progress_payment = 'progress_payment_term'
            count_progress_payment = 0
            
            retention_payment = 'retention_payment_term'
            count_retention_payment =0
            
            after_compltetion_payment =  'after_compltetion_payment_term'
            count_after_compltetion_payment = 0
            
            schedule_payment = 'schedule_payment_term'
            count_schedule_payment = 0
            
            for ctc_id,type,name,value_amount,seq in cr.fetchall():
                if type=='adv':
                    res[ctc_id]['count_down_payment'] = res[ctc_id]['count_down_payment'] + 1
                    res[ctc_id][down_payment] = res[ctc_id][down_payment] + list_number[res[ctc_id]['count_down_payment']] % str(value_amount)
                elif type=='p':
                    res[ctc_id]['count_progress_payment'] = res[ctc_id]['count_progress_payment'] +1
                    res[ctc_id][progress_payment] = res[ctc_id][progress_payment] +  list_number[res[ctc_id]['count_progress_payment']] % str(value_amount)
                elif type=='re':
                    res[ctc_id]['count_retention_payment'] = res[ctc_id]['count_retention_payment'] +1
                    res[ctc_id][retention_payment] = res[ctc_id][retention_payment] +  list_number[res[ctc_id]['count_retention_payment']] % str(value_amount)
                elif type=='sch':
                    res[ctc_id]['count_schedule_payment'] = res[ctc_id]['count_schedule_payment'] +1
                    res[ctc_id][schedule_payment] = res[ctc_id][schedule_payment] +  list_number[res[ctc_id]['count_schedule_payment']] % str(value_amount)
                else:
                    res[ctc_id]['count_after_compltetion_payment'] = res[ctc_id]['count_after_compltetion_payment'] +1
                    res[ctc_id][after_compltetion_payment] =  res[ctc_id][after_compltetion_payment] +  list_number[res[ctc_id]['count_after_compltetion_payment'] ] % str(value_amount)
        #raise osv.except_osv("E",res)
        return res
    
    def _get_payment_term_line(self, cr, uid, ids, context=None):
        result = {}
        for terms in self.pool.get('kderp.contract.payment.term.line').browse(cr, uid, ids, context=context):
            result[terms.contract_id.id] = True
        return result.keys()
    
    def _get_payment_term_code(self, cr, uid, ids, context=None):
        result = {}
        for term_codes in self.pool.get('kderp.payment.term.code').browse(cr, uid, ids, context=context):
            result[term_codes.contract_term_line.contract_id.id] = True
        return result.keys()
    
    def _get_contract_status(self, cr, uid, ids, name1, args, context={}):
        res = {}
        for ctc in self.browse(cr,uid,ids):
            st = 'u'
            for ctc_summary in ctc.contract_summary_ids:
                if ctc_summary.contracted_amount>0 and ctc_summary.contracted_amount==ctc_summary.claimed_amount and ctc_summary.claimed_amount==ctc_summary.received_amount:
                    st = 'c'
            res[ctc.id] = st 
        return res
    
    _columns={
                'contract_no':fields.char('Number',size=50,required=True),
                'newcode_suggest':fields.selection(_newcode_suggest,'New Code',size=128,store=False),
                
                'combine_term':fields.function(_get_payment_term,method=True,type='text',string='Combine Term Name'),
                
                'date':fields.date('Date of contract',select=2),
                'registration_date':fields.date("Reg. Date",select=2),
                'started_date':fields.date('Started Date',select=2),
                'completion_date':fields.date('Completion Date',select=2),
                'closed_date':fields.date('Closed Date',select=2),
                'contract_ref':fields.char('Contract Ref.',size=100),
                'project_name':fields.char('Prj. Name',size=255,required=True),
                'description':fields.char('Description',size=250,required=True),
                'progressno':fields.integer('Progress No'), #Temp for store                 
                'contract_rate':fields.float("Ex.Rate",readonly=True,digits=(16,2)),
                'quotations':fields.function(_get_quotation_info,type='char',
                                             size='128',method=True,string='Quotations'),#,multi='_quo_info'
                #'scopeofwork':fields.function(_get_quotation_info,type='char',
                 #                            size='128',method=True,string='Scope Of Work',multi='_quo_info'),                                             
                
                ## Relation Fields
                #'project_ids':fields.one2many('kdvn.project','contract_id','Project'),
                #'contract_currency_ids':fields.one2many('project.currency','contract_id','Project Currencies'),
                #'payment_term':fields.one2many('kdvn.payment.term.line','contract_id','Payment Term'),
                #'progress_line': fields.one2many('kdvn.progress','contract_id',"Progress"),
                'payment_ids':fields.one2many("kdvn.payment.from.client",'contract_id','Payment',readonly=True),
                'quotation_ids':fields.function(_get_quotation_lists,relation='sale.order',type='one2many',string='Quotations',method=True),
                #'quotation_ids':fields.one2many('sale.order','contract_id','Quotations'),
                
                'owner_id':fields.many2one('res.partner','Owner'),
                'client_id':fields.many2one('res.partner','Client'),
                'address_id':fields.many2one('res.partner.address','Address'),
                'invoice_address_id':fields.many2one('res.partner.address','Invoice Address'),
                
#                'contract_currency':fields.function(_get_contract_currency,
#                                                    type='many2one',
#                                                    relation="project.currency",
#                                                    string="Cur.",
#                                                    readonly=True,
#                                                    method=True,
#                                                    store=True,
#                                                    help='Use below Currency System to update'),

                'status':fields.function(_get_contract_status,selection=[('c','Completed'),('u','Uncompleted')],type='selection',string='Status',method=True), #If upgrade to OE6 must be change to State [('completed','Completed'),('uncompleted','Uncompleted')]
                'outstanding':fields.selection([('none','None'),('pm_check','PM Check'),('bc_check','BC Check')],'Outstanding',select=2),
                
                'contract_amount_e':fields.float('Contract Amt. E.',digits=(16,2),readonly=True),
                'contract_amount_m':fields.float('Contract Amt. M.',digits=(16,2),readonly=True),
                'contract_vat':fields.float('Contract V.A.T.',digits=(16,2),readonly=True),
                'contract_total':fields.float('Contract Total',digits=(16,2),readonly=True),
                'contract_total_manual':fields.float('Contract Total',digits=(16,2)),
                
                'attached_contract_sent':fields.boolean('Contract Sent'),
                'attached_contract_received':fields.boolean('Contract Received'),
                'attached_approved_quotation':fields.boolean("Approved Quotation"),

                
                'currency_line':fields.one2many('kderp.contract.currency.line','contract_id','Currency line',context={'typeofrate':'tax_office'}),
                'kderp_contract_payment_term_line':fields.one2many('kderp.contract.payment.term.line','contract_id','Payment Term'),
                
                'taxes_percent':fields.selection(_get_tax_percents,"VAT (%)",select=2,states={'cancel': [('readonly', True)]}),
                'with_holding_taxes_percent':fields.selection(_get_tax_percents,"W.H. Taxes (%)",select=2,states={'cancel': [('readonly', True)]}),
                
                'contract_summary_line_ids':fields.one2many('kderp.contract.summary.line','contract_id','Summary',readonly=True),
                'contract_summary_ids':fields.one2many('kderp.contract.summary','contract_id','Summary',readonly=True),
                'project_estimated_ids':fields.one2many('kderp.contract.estimated','contract_id','Project Estimated',readonly=True),
                
                'project_estimated_currency_ids':fields.one2many('kderp.project.contract.estimated.currency','contract_id','Project Estimated Cur.',readonly=True),
               
                'contract_summary_cols_ids':fields.one2many("kderp.contract.amount.to.cols",'contract_id','Summary'),
                
                'down_payment_term':fields.function(_get_payment_terms,type='text',method=True,string='Down Payment',multi='_get_payment_terms',store=
                                                                {'kdvn.contract.client':(lambda self, cr, uid, ids, c={}: ids, None, 20),
                                                                 'kderp.contract.payment.term.line': (_get_payment_term_line, ['term_id','value_amount'], 20)}),
                'progress_payment_term':fields.function(_get_payment_terms,type='text',method=True,string='Progress Payment',multi='_get_payment_terms',store=
                                                                {'kdvn.contract.client':(lambda self, cr, uid, ids, c={}: ids, None, 20),
                                                                 'kderp.contract.payment.term.line': (_get_payment_term_line, ['term_id','value_amount'], 20)}),
                'retention_payment_term':fields.function(_get_payment_terms,type='text',method=True,string='Retention Payment',multi='_get_payment_terms',store=
                                                                {'kdvn.contract.client':(lambda self, cr, uid, ids, c={}: ids, None, 20),
                                                                 'kderp.contract.payment.term.line': (_get_payment_term_line, ['term_id','value_amount'], 20)}),
                'after_compltetion_payment_term':fields.function(_get_payment_terms,type='text',method=True,string='After Completion Payment',multi='_get_payment_terms',store=
                                                                {'kdvn.contract.client':(lambda self, cr, uid, ids, c={}: ids, None, 20),
                                                                 'kderp.contract.payment.term.line': (_get_payment_term_line, ['term_id','value_amount'], 20)}),
                'schedule_payment_term':fields.function(_get_payment_terms,type='text',method=True,string='Schedule Payment',multi='_get_payment_terms',store=
                                                                {'kdvn.contract.client':(lambda self, cr, uid, ids, c={}: ids, None, 20),
                                                                 'kderp.contract.payment.term.line': (_get_payment_term_line, ['term_id','value_amount'], 20)}),

                }
    _sql_constraints = [('kdvn_unique_contract_no', 'unique(contract_no)', 'KDVN Error: The contract number must be unique!')]
    #On change for view area
    def _onchange_client_id(self, cr, uid, ids, partner_id):
        invoice_addr_id = False
        address_id = False
        if partner_id:
            res = self.pool.get('res.partner').address_get(cr, uid, [partner_id], ['default', 'invoice'])
            invoice_addr_id = res['invoice']
            address_id = False
            if res['default']:
                address_id = res['default'] 
            if not invoice_addr_id and address_id:  
                invoice_addr_id =address_id
                
        result = {'value': {'invoice_address_id': invoice_addr_id,
                            'address_id':address_id}}
        return result
    
    def _onchange_suggest_code(self, cr, uid, ids,new_code):
        if new_code:
            val={'value':{'contract_no':new_code,'newcode_suggest':False}}
        else:
            val={}
        return val
    
    #Trigger when create new Contract
#    def create(self, cr, uid, vals, context={}):        
#        id_test=osv.osv.create(self, cr, uid, vals, context=context)
#        
#        kdvn_payment_term_line_obj=self.pool.get('kdvn.payment.term.line')         
#        kdvn_payment_line_id=kdvn_payment_term_line_obj.create(cr,uid,{'value_amount':100.0,'name':'Payment',
#                                                 'sequence':1,'contract_id':id_test})
#        if context:
#            if 'currency_ids' in context:
#                if len(context['currency_ids'])>0:
#                    new_id=self.pool.get('kderp.contract.currency.line')._create_currency(cr,uid,id_test,context)
#        return id_test    contract
kdvn_contract_client()

class kderp_contract_currency_line(osv.osv):
    _name = "kderp.contract.currency.line"
    _description="KDERP Contract Currency Line"
    
#    def _create_currency(self,cr,uid,ctc_id,context={}):
#        vals = []
#        new_id = False
#        if context:
#            if 'currency_ids' in context:
#                for curr in context['currency_ids']:
#                    new_id = self.create(cr,uid,{'contract_id':ctc_id,'currency_id':curr},context)
#        return new_id
    
    def _get_contract_info(self, cr, uid, ids, name, arg, context=None):
         res = {}
         if ids:
             ctc_line = {'contracted_amount':0.0,'claimed_amount':0.0,'received_amount':0.0}
             for x in self.browse(cr,uid,ids):
                 cr.execute("""Select 
                                sum(coalesce(vjfc.amount,0)) as contracted_amount,
                                coalesce(claimed_subtotal,0) as claimed_amount,
                                coalesce(received_amount,0) as received_amount
                            from
                                kdvn_contract_client kcc
                            left join
                                kderp_contract_currency_line  kccl on kcc.id = kccl.contract_id
                            left join
                                vwquotation_job_for_contract vjfc on kcc.id = vjfc.contract_id and kccl.currency_id = vjfc.currency_id
                            left join --Claimed Amount follow currency
                                (Select 
                                    contract_id,
                                    kpfc.currency_id,
                                    sum(kpfcd.amount) as claimed_subtotal
                                from 
                                    kdvn_payment_from_client kpfc
                                left join
                                    kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                                where
                                    state not in ('draft','cancel')
                                group by
                                    kpfc.currency_id,
                                    contract_id) vwclaimed on kcc.id = vwclaimed.contract_id and kccl.currency_id = vwclaimed.currency_id
                            left join --Received Amount in Company Currency
                                (Select 
                                    contract_id,
                                    kpfc.currency_id,
                                    sum(kpfcd.amount) as received_amount
                                from
                                    kdvn_payment_from_client kpfc
                                left join
                                    kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                                left join
                                    (Select distinct payment_id from kdvn_received kr) kr on kpfc.id = kr.payment_id
                                where
                                    state not in ('draft','cancel') and
                                    coalesce(kr.payment_id,0)>0
                                group by
                                    kpfc.currency_id,
                                    contract_id) vwreceived on kcc.id = vwreceived.contract_id and kccl.currency_id = vwreceived.currency_id
                            where
                                kcc.id in (%s) and kccl.currency_id in (%s) 
                            group by
                                kcc.id,
                                kccl.currency_id,
                                claimed_amount,
                                received_amount""" % (x.contract_id.id,x.currency_id.id))
                 tmp = cr.dictfetchall()
                 if tmp:
                     res[x.id] = tmp[0]
                 else:
                     res[x.id] = ctc_line
         return res
    

    _columns = {
                'currency_id':fields.many2one('kderp.currency','Cur.',required=True,context={'typeofrate':'tax_office'}),
                'contract_id':fields.many2one('kdvn.contract.client','Contract',required=True),
                'contracted_amount':fields.function(_get_contract_info,string='Contracted.',method=True,multi='_get_amount',type='float'),
                'claimed_amount':fields.function(_get_contract_info,string='Claimed',method=True,multi='_get_amount',type='float'),
                'received_amount':fields.function(_get_contract_info,string='Received',method=True,multi='_get_amount',type='float'),
#                'wht_amount':fields.function(_get_est_contract_amount,string='W/H Amt.',method=True,multi='_get_amount',type='float'),
                }
    _sql_constraints = [('kderp_currency_line_unique', 'unique(currency_id)', 'KDERP Error: The currency must be unique!')]
kderp_contract_currency_line()

class kderp_project_contract_estimated_currency(osv.osv):
    _name='kderp.project.contract.estimated.currency'
    _description = 'KDERP Project Contract Estimated Estimated'
    _auto = False
    _table="vwquotation_job_for_contract"
    
    _columns = {
                'contract_id':fields.many2one('kdvn.contract.client','Contract'),
                'project_id':fields.many2one('kdvn.project','Project'),
                'currency_id':fields.many2one('kderp.currency','Cur.'),
                'amount':fields.float('Amt.')
                }
    def init(self,cr):
        cr.execute("""Create or replace view vwquotation_job_for_contract as 
                        Select
                            row_number() over (order by contract_id,currency_id,project_id) as id,
                            contract_id,
                            currency_id,
                            project_id,
                            sum(amount) as amount
                        from
                            (Select 
                                so.contract_id,
                                sol.currency_id,
                                project_e_id as project_id,
                                case when coalesce(kccl.id,0)>0 then coalesce(sol.amount,0)+coalesce(sol.discount,0) else 0 end as amount
                            from 
                                sale_order so
                            left join
                                sale_order_line sol on so.id = sol.order_id and job_type='E'
                            left join
                                kderp_contract_currency_line kccl on so.contract_id = kccl.contract_id and sol.currency_id = kccl.currency_id
                            where 
                                so.state not in ('draft','cancel') and
                                coalesce(project_e_id,0)>0 and coalesce(so.contract_id,0)>0
                            Union all
                            Select 
                                so.contract_id,
                                sol.currency_id,
                                project_m_id as project_id,
                                case when coalesce(kccl.id,0)>0 then coalesce(sol.amount,0)+coalesce(sol.discount,0) else 0 end as amount
                            from 
                                sale_order so
                            left join
                                sale_order_line sol on so.id = sol.order_id and job_type='M'
                            left join
                                kderp_contract_currency_line kccl on so.contract_id = kccl.contract_id and sol.currency_id = kccl.currency_id  
                            where 
                                so.state not in ('draft','cancel') and 
                                coalesce(project_m_id,0)>0 and coalesce(so.contract_id,0)>0
                            union all
                            Select 
                                so.contract_id,
                                get_default_currency() as currency_id,
                                project_e_id as project_id,
                                sum(coalesce(fncompute(sol.currency_id,get_default_currency(),dateofsubmit,'tax_office'),0)*(coalesce(sol.amount,0)+coalesce(sol.discount,0))) as amount
                            from 
                                sale_order so
                            left join
                                sale_order_line sol on so.id = sol.order_id and job_type='E'
                            where 
                                so.state not in ('draft','cancel') and 
                                coalesce(project_e_id,0)>0 and coalesce(so.contract_id,0)>0 and 
                                sol.currency_id not in (Select currency_id from kderp_contract_currency_line kccl where kccl.contract_id = so.contract_id)
                            group by
                                get_default_currency(),
                                so.contract_id,
                                project_e_id
                            Union all
                            Select 
                                so.contract_id,
                                get_default_currency() as currency_id,
                                project_m_id as project_id,
                                sum(coalesce(fncompute(sol.currency_id,get_default_currency(),dateofsubmit,'tax_office'),0)*(coalesce(sol.amount,0)+coalesce(sol.discount,0))) as amount
                            from 
                                sale_order so
                            left join
                                sale_order_line sol on so.id = sol.order_id and job_type = 'M'
                            where 
                                so.state not in ('draft','cancel') and 
                                coalesce(project_m_id,0)>0 and coalesce(so.contract_id,0)>0 and 
                                sol.currency_id not in (Select currency_id from kderp_contract_currency_line kccl where kccl.contract_id = so.contract_id)
                            group by
                                get_default_currency(),
                                so.contract_id,
                                project_m_id) vwcombined 
                        group by  
                            contract_id,
                            currency_id,
                            project_id""")
kderp_project_contract_estimated_currency()


class kderp_contract_estimated(osv.osv):
    _name='kderp.contract.estimated'
    _description = 'KDERP Contract Estimated'
    _auto = False
    
    def _get_job_ref(self, cr, uid, ids, name, args, context=None):
        res = {}
        for ctc in self.browse(cr,uid,ids):
            prj_id = ctc.project_id
            ctc_id = ctc.contract_id
            cr.execute("""Select
                            sum(code || ';') as job_ref
                        from
                            (Select 
                                so.contract_id,
                                project_e_id as job_id
                            from 
                                sale_order so
                            where
                                coalesce(so.contract_id,0)=%d and coalesce(project_e_id,0)>0 and state not in ('draft','cancel')
                            group by
                                so.contract_id,project_e_id
                            union
                            Select 
                                so.contract_id,
                                project_m_id as job_id
                            from 
                                sale_order so
                            where
                                coalesce(so.contract_id,0)=%d and coalesce(project_m_id,0)>0 and state not in ('draft','cancel')
                            group by
                                so.contract_id,project_m_id) vwcombine
                        left join
                            kdvn_project kp on job_id = kp.id and kp.id<>%d 
                        group by
                            vwcombine.contract_id""" % (ctc_id,ctc_id,prj_id))
            r = cr.fetchone()
            job_ref = ''
            if r:
                job_ref = r[0]
            res [ctc.id] = job_ref
        return res
    
    def _get_quotations(self, cr, uid, ids, name, args, context=None):
        res = {}
        for ctc in self.browse(cr,uid,ids):
            prj_id = ctc.project_id
            ctc_id = ctc.contract_id
            cr.execute("""Select
                            sum(name || ';') as quotation_nos
                        from
                            (Select
                                name,
                                so.contract_id,
                                project_e_id as job_id
                            from
                                sale_order so
                            where
                                contract_id=%d and project_e_id = %d and state not in ('draft','cancel')
                            Union
                            Select
                                name,
                                so.contract_id,
                                project_m_id as job_id
                            from
                                sale_order so
                            where
                                contract_id = %d and project_m_id = %d and state not in ('draft','cancel')) vwcombine
                        group by
                            contract_id,
                            job_id""" % (ctc_id,prj_id,ctc_id,prj_id))
            r = cr.fetchone()
            quo_ref = ''
            if r:
                quo_ref = r[0]
            res [ctc.id] = quo_ref
        return res
    
    _columns = {
                'contract_id':fields.many2one('kdvn.contract.client','Contract'),
                'project_id':fields.many2one('kdvn.project','Project'),
                'idr':fields.float('IDR'),
                'usd':fields.float('USD'),
                'jpy':fields.float('JPY'),
                'sgd':fields.float('SGD'),
                'eur':fields.float('EUR'),
                
                'claimed_idr':fields.float('Claimed (IDR)'),                
                'claimed_usd':fields.float('Claimed (USD)'),
                'claimed_jpy':fields.float('Claimed (JPY)'),
                'claimed_sgd':fields.float('Claimed (SGD)'),
                'claimed_eur':fields.float('Claimed (EUR)'),
                
                'job_ref':fields.function(_get_job_ref,type='text',method=True,string='Job Ref.'),
                'quotation_nos':fields.function(_get_quotations,type='text',method=True,string='Quotations'),
                }
    def init(self,cr):
#        cr.execute("""Create or replace view vwquotation_job_for_contract as 
#                        Select 
#                            so.contract_id,
#                            sol.currency_id,
#                            project_e_id as project_id,
#                            case when coalesce(kccl.id,0)>0 then coalesce(sol.amount,0)+coalesce(sol.discount,0) else 0 end as amount
#                        from 
#                            sale_order so
#                        left join
#                            sale_order_line sol on so.id = sol.order_id and job_type='E'
#                        left join
#                            kderp_contract_currency_line kccl on so.contract_id = kccl.contract_id and sol.currency_id = kccl.currency_id
#                        where 
#                            so.state not in ('draft','cancel') and
#                            coalesce(project_e_id,0)>0 and coalesce(so.contract_id,0)>0
#                        Union all
#                        Select 
#                            so.contract_id,
#                            sol.currency_id,
#                            project_m_id as project_id,
#                            case when coalesce(kccl.id,0)>0 then coalesce(sol.amount,0)+coalesce(sol.discount,0) else 0 end as amount
#                        from 
#                            sale_order so
#                        left join
#                            sale_order_line sol on so.id = sol.order_id and job_type='M'
#                        left join
#                            kderp_contract_currency_line kccl on so.contract_id = kccl.contract_id and sol.currency_id = kccl.currency_id  
#                        where 
#                            so.state not in ('draft','cancel') and 
#                            coalesce(project_m_id,0)>0 and coalesce(so.contract_id,0)>0
#                        union all
#                        Select 
#                            so.contract_id,
#                            get_default_currency() as currency_id,
#                            project_e_id as project_id,
#                            sum(coalesce(fncompute(sol.currency_id,get_default_currency(),dateofsubmit,'tax_office'),0)*(coalesce(sol.amount,0)+coalesce(sol.discount,0))) as amount
#                        from 
#                            sale_order so
#                        left join
#                            sale_order_line sol on so.id = sol.order_id and job_type='E'
#                        where 
#                            so.state not in ('draft','cancel') and 
#                            coalesce(project_e_id,0)>0 and coalesce(so.contract_id,0)>0 and 
#                            sol.currency_id not in (Select currency_id from kderp_contract_currency_line kccl where kccl.contract_id = so.contract_id)
#                        group by
#                            get_default_currency(),
#                            so.contract_id,
#                            project_e_id
#                        Union all
#                        Select 
#                            so.contract_id,
#                            get_default_currency() as currency_id,
#                            project_m_id as project_id,
#                            sum(coalesce(fncompute(sol.currency_id,get_default_currency(),dateofsubmit,'tax_office'),0)*(coalesce(sol.amount,0)+coalesce(sol.discount,0))) as amount
#                        from 
#                            sale_order so
#                        left join
#                            sale_order_line sol on so.id = sol.order_id and job_type = 'M'
#                        where 
#                            so.state not in ('draft','cancel') and 
#                            coalesce(project_m_id,0)>0 and coalesce(so.contract_id,0)>0 and 
#                            sol.currency_id not in (Select currency_id from kderp_contract_currency_line kccl where kccl.contract_id = so.contract_id)
#                        group by
#                            get_default_currency(),
#                            so.contract_id,
#                            project_m_id;""")
        
        cr.execute("""create or replace view kderp_contract_estimated as 
                        Select
                            row_number() over (order by vjfc.contract_id,vjfc.project_id)::int as id,
                            vjfc.contract_id,
                            vjfc.project_id,
                            sum(case when kc.code='IDR' then amount else 0 end) as idr,
                            sum(case when kc.code='USD' then amount else 0 end) as usd,
                            sum(case when kc.code='EUR' then amount else 0 end) as eur,
                            sum(case when kc.code='SGD' then amount else 0 end) as sgd,
                            sum(case when kc.code='JPY' then amount else 0 end) as jpy,
                            sum(case when kc.code='IDR' then claimed_subtotal else 0 end) as claimed_idr,
                            sum(case when kc.code='USD' then claimed_subtotal else 0 end) as claimed_usd,
                            sum(case when kc.code='SGD' then claimed_subtotal else 0 end) as claimed_sgd,
                            sum(case when kc.code='JPY' then claimed_subtotal else 0 end) as claimed_jpy,
                            sum(case when kc.code='EUR' then claimed_subtotal else 0 end) as claimed_eur
                        from
                            vwquotation_job_for_contract vjfc
                        left join
                            kderp_currency kc on currency_id = kc.id
                        left join
                            (Select 
                                contract_id,
                                project_id,
                                kpfc.currency_id,
                                sum(kpfcd.amount) as claimed_subtotal
                                from 
                                kdvn_payment_from_client kpfc
                                left join
                                kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                                where
                                state not in ('draft','cancel')
                                group by
                                project_id,
                                kpfc.currency_id,
                                contract_id) vwclaimed on vjfc.contract_id = vwclaimed.contract_id and vjfc.project_id=vwclaimed.project_id and vjfc.currency_id = vwclaimed.currency_id
                        group by
                            vjfc.contract_id,
                            vjfc.project_id;""")
kderp_contract_estimated()

class kderp_contract_payment_term_line(osv.osv):
    _name = "kderp.contract.payment.term.line"
    _description = "KDERP Contract Payment Term Line"
    _rec_name = 'term_id'
    _order='sequence'
    
    def _new_seq(self, cr, uid, context={}):
        a=1
        if context:
            if 'contract_id' in context:
                payment_line_ids=self.search(cr,uid,[('contract_id','=',context['contract_id'])])
                payments=self.read(cr,uid,payment_line_ids,['sequence'],context)
                a=0
                for sq in payments:
                    if sq['sequence']>a:
                        a=sq['sequence']
                a=a+5
        return a
    
    _columns ={
                 'contract_id':fields.many2one('kdvn.contract.client','Contract'),
                 'sequence':fields.integer('Seq.',required=True),
                 'value_amount':fields.float('Percent (%)'),
                 'term_id':fields.many2one('kderp.payment.term.code','P.Term',required=True),
                 'description':fields.char('Desc.',size=64),
                 'due_date':fields.date('Due Date')
                 }
    _defaults =  {
         'sequence': _new_seq
         }
    
    def name_get(self, cr, uid, ids, context={}):
        if not len(ids):
            return []
        reads = self.read(cr, uid, ids, ['term_id','value_amount'], context)
        res = []
        for record in reads:
            #raise osv.except_osv("E",record['term_id'][0])
            name = self.pool.get('kderp.payment.term.code').name_get(cr,uid,[record['term_id'][0]])
            rs = False
            if name:
                rs = name[0][1]
            res.append((record['id'], rs))
        return res
    
    def _onchange_payment_term_id(self, cr, uid, ids, term_id):
        value_amount = 0.0
        if term_id:
            value_amount = 0.0
            res = self.pool.get('kderp.payment.term.code').read(cr,uid,term_id,['value_amount'])
            #raise osv.except_osv("E",res)
            if res:
                value_amount = res['value_amount']
                
        result = {'value': {'value_amount': value_amount}}
        return result
    
kderp_contract_payment_term_line()

class kderp_contract_summary(osv.osv):
    _name='kderp.contract.summary'
    _description = 'KERP Contract Summary'
    _auto = False
    _columns={
              'contract_id':fields.many2one('kdvn.contract.client','Contract'),
              'contracted_amount':fields.float('Contracted Amt.'),
              'claimed_amount':fields.float('Claimed Amt.'),
              'received_amount':fields.float('Received Amt.'),
              
              'receivable_amount':fields.float('Receivable Amt.'),
              'balance':fields.float('Balance'),
              }
    
    def init(self,cr):
        cr.execute("""create or replace view kderp_contract_summary as 
                        Select 
                            kcc.id as id,
                            kcc.id as contract_id,
                            sum(coalesce(fncompute(vjfc.currency_id,get_default_currency(),kcc.registration_date,'tax_office'),0)
                            *(coalesce(vjfc.amount,0) - coalesce(claimed_subtotal,0)))+coalesce(claimed_amount_company_curr,0) as contracted_amount,
                            coalesce(claimed_amount_company_curr,0) as claimed_amount,
                            sum(coalesce(fncompute(vjfc.currency_id,get_default_currency(),kcc.registration_date,'tax_office'),0)
                            *(coalesce(vjfc.amount,0) - coalesce(claimed_subtotal,0)))+coalesce(claimed_amount_company_curr,0)
                              - coalesce(claimed_amount_company_curr,0) as balance,
                            coalesce(received_amount,0) as received_amount,
                            coalesce(claimed_amount_company_curr,0) - coalesce(received_amount,0) as receivable_amount 
                        from
                            kdvn_contract_client kcc
                        left join
                            (Select 
                                contract_id,
                                currency_id,
                                sum(amount) as amount 
                            from 
                                vwquotation_job_for_contract vjfc 
                            group by contract_id,currency_id) vjfc on kcc.id = vjfc.contract_id
                        left join --Claimed Amount follow currency
                            (Select 
                                contract_id,
                                kpfc.currency_id,
                                sum(kpfcd.amount) as claimed_subtotal
                            from 
                                kdvn_payment_from_client kpfc
                            left join
                                kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                            where
                                state not in ('draft','cancel')
                            group by
                                kpfc.currency_id,
                                contract_id) vwclaimed on kcc.id = vwclaimed.contract_id and vjfc.currency_id = vwclaimed.currency_id
                        left join --Claimed Amount in Company Currency
                            (Select 
                                contract_id,
                                sum(fncompute(kpfc.currency_id,get_default_currency(),date,'tax_office')*kpfcd.amount) as claimed_amount_company_curr
                            from
                                kdvn_payment_from_client kpfc
                            left join
                                kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                            where
                                state not in ('draft','cancel')
                            group by
                                contract_id) vwclaimed_calculated on kcc.id = vwclaimed_calculated.contract_id
                        left join --Received Amount in Company Currency
                            (Select 
                                contract_id,
                                sum(fncompute(kpfc.currency_id,get_default_currency(),date,'tax_office')*kpfcd.amount) as received_amount
                            from
                                kdvn_payment_from_client kpfc
                            left join
                                kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                            left join
                                (Select distinct payment_id from kdvn_received kr) kr on kpfc.id = kr.payment_id
                            where
                                state not in ('draft','cancel') and
                                coalesce(kr.payment_id,0)>0
                            group by
                                contract_id) vwreceived on kcc.id = vwreceived.contract_id
                        group by
                            received_amount,
                            claimed_amount_company_curr,
                            kcc.id;""")
kderp_contract_summary()

class kderp_contract_summary_line(osv.osv):
    _name='kderp.contract.summary.line'
    _description = 'KERP Contract Summary Line'
    _auto = False
    _columns={
              'contract_id':fields.many2one('kdvn.contract.client','Contract'),
              'project_id':fields.many2one('kdvn.project','Job'),
              'contracted_amount':fields.float('Contracted Amt.'),
              'claimed_amount':fields.float('Claimed Amt.'),
              'received_amount':fields.float('Received Amt.'),
              }
    
    def init(self,cr):
        cr.execute("""create or replace view kderp_contract_summary_line as 
                    Select
                        row_number()  over (order by contract_id,project_id) as id,
                        contract_id,
                        project_id,
                        sum(remain_contracted_amount)+claimed_amount as contracted_amount,
                        claimed_amount,
                        received_amount
                    from
                        (Select 
                            kcc.id as contract_id,
                            vjfc.project_id,
                            vjfc.currency_id,
                            (sum(vjfc.amount)- coalesce(claimed_subtotal,0))*coalesce(fncompute(vjfc.currency_id,get_default_currency(),kcc.registration_date,'tax_office'),0) as remain_contracted_amount,
                            coalesce(claimed_amount_company_curr,0) as claimed_amount,
                            coalesce(received_amount,0) as received_amount
                        from
                            kdvn_contract_client kcc
                        left join
                            vwquotation_job_for_contract vjfc on kcc.id = vjfc.contract_id
                        left join --Claimed Amount follow currency
                            (Select 
                                contract_id,
                                project_id,
                                kpfc.currency_id,
                                sum(kpfcd.amount) as claimed_subtotal
                            from 
                                kdvn_payment_from_client kpfc
                            left join
                                kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                            where
                                state not in ('draft','cancel')
                            group by
                                project_id,
                                kpfc.currency_id,
                                contract_id) vwclaimed on kcc.id = vwclaimed.contract_id and vjfc.project_id=vwclaimed.project_id and vjfc.currency_id = vwclaimed.currency_id
                        left join --Claimed Amount in Company Currency
                            (Select 
                                contract_id,
                                project_id,
                                sum(fncompute(kpfc.currency_id,get_default_currency(),date,'tax_office')*kpfcd.amount) as claimed_amount_company_curr
                            from
                                kdvn_payment_from_client kpfc
                            left join
                                kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                            where
                                state not in ('draft','cancel')
                            group by
                                project_id,
                                contract_id) vwclaimed_calculated on kcc.id = vwclaimed_calculated.contract_id and vjfc.project_id=vwclaimed_calculated.project_id
                        left join --Received Amount in Company Currency
                            (Select 
                                contract_id,
                                project_id,
                                sum(fncompute(kpfc.currency_id,get_default_currency(),date,'tax_office')*kpfcd.amount) as received_amount
                            from
                                kdvn_payment_from_client kpfc
                            left join
                                kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                            left join
                                (Select distinct payment_id from kdvn_received kr) kr on kpfc.id = kr.payment_id
                            where
                                state not in ('draft','cancel') and
                                coalesce(kr.payment_id,0)>0
                            group by
                                project_id,
                                contract_id) vwreceived on kcc.id = vwreceived.contract_id and vjfc.project_id = vwreceived.project_id
                        where
                            coalesce(vjfc.project_id,0)>0
                        group by
                            received_amount,
                            claimed_subtotal,
                            claimed_amount_company_curr,
                            vjfc.currency_id,
                            kcc.id,
                            vjfc.project_id) vwtotal
                    Group by
                        contract_id,
                        project_id,
                        claimed_amount,
                        received_amount;""")
kderp_contract_summary_line()

class kderp_contract_amount_to_cols(osv.osv):
    _name = "kderp.contract.amount.to.cols"
    _auto = False
    _description = "KDERP Contract Amount to Cols"
    
    def _get_contract_summary_amount(self, cr, uid, ids, name, arg, context=None):
        res = {}
        if ids:
             ctc_line = {'contracted_idr':0.0,'claimed_idr':0.0,'received_idr':0.0,
                         'contracted_usd':0.0,'claimed_usd':0.0,'received_usd':0.0,
                         'contracted_jpy':0.0,'claimed_jpy':0.0,'received_jpy':0.0,
                         'contracted_sgd':0.0,'claimed_sgd':0.0,'received_sgd':0.0,
                         'contracted_eur':0.0,'claimed_eur':0.0,'received_eur':0.0}
             for id in ids:
                 res[id] = ctc_line
                 ctc_ids = id
                 cr.execute("""Select 
                                vwcontract.id,
                                sum(case when kc.code='IDR' then contracted_amount else 0 end) as contracted_idr,
                                sum(case when kc.code='IDR' then claimed_amount else 0 end) as claimed_idr,
                                sum(case when kc.code='IDR' then received_amount else 0 end) as received_idr,
                            
                                sum(case when kc.code='USD' then contracted_amount else 0 end) as contracted_usd,
                                sum(case when kc.code='USD' then claimed_amount else 0 end) as claimed_usd,
                                sum(case when kc.code='USD' then received_amount else 0 end) as received_usd,
                            
                                sum(case when kc.code='JPY' then contracted_amount else 0 end) as contracted_jpy,
                                sum(case when kc.code='JPY' then claimed_amount else 0 end) as claimed_jpy,
                                sum(case when kc.code='JPY' then received_amount else 0 end) as received_jpy,
                            
                                sum(case when kc.code='EUR' then contracted_amount else 0 end) as contracted_eur,
                                sum(case when kc.code='EUR' then claimed_amount else 0 end) as claimed_eur,
                                sum(case when kc.code='EUR' then received_amount else 0 end) as received_eur,
                            
                                sum(case when kc.code='SGD' then contracted_amount else 0 end) as contracted_sgd,
                                sum(case when kc.code='SGD' then claimed_amount else 0 end) as claimed_sgd,
                                sum(case when kc.code='SGD' then received_amount else 0 end) as received_sgd    
                            from 
                                (Select 
                                    kcc.id,
                                    kccl.currency_id,
                                    sum(coalesce(vjfc.amount,0)) as contracted_amount,
                                    coalesce(claimed_subtotal,0) as claimed_amount,
                                    coalesce(received_amount,0) as received_amount
                                from
                                    kdvn_contract_client kcc
                                left join
                                    kderp_contract_currency_line  kccl on kcc.id = kccl.contract_id
                                left join
                                    vwquotation_job_for_contract vjfc on kcc.id = vjfc.contract_id and kccl.currency_id = vjfc.currency_id
                                left join --Claimed Amount follow currency
                                    (Select 
                                        contract_id,
                                        kpfc.currency_id,
                                        sum(kpfcd.amount) as claimed_subtotal
                                    from 
                                        kdvn_payment_from_client kpfc
                                    left join
                                        kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                                    where
                                        state not in ('draft','cancel') and contract_id in (%s)
                                    group by
                                        kpfc.currency_id,
                                        contract_id) vwclaimed on kcc.id = vwclaimed.contract_id and kccl.currency_id = vwclaimed.currency_id
                                left join --Received Amount in Company Currency
                                    (Select 
                                        contract_id,
                                        kpfc.currency_id,
                                        sum(kpfcd.amount) as received_amount
                                    from
                                        kdvn_payment_from_client kpfc
                                    left join
                                        kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                                    left join
                                        (Select distinct payment_id from kdvn_received kr) kr on kpfc.id = kr.payment_id
                                    where
                                        state not in ('draft','cancel') and
                                        coalesce(kr.payment_id,0)>0 and contract_id in (%s)
                                    group by
                                        kpfc.currency_id,
                                        contract_id) vwreceived on kcc.id = vwreceived.contract_id and kccl.currency_id = vwreceived.currency_id
                                where kcc.id in (%s)
                                group by
                                    kcc.id,
                                    kccl.currency_id,
                                    claimed_amount,
                                    received_amount) vwcontract
                            left join
                                kderp_currency kc on vwcontract.currency_id = kc.id
                            where 
                                vwcontract.id in (%s)
                            group by
                                vwcontract.id""" % (ctc_ids,ctc_ids,ctc_ids,ctc_ids))
                 for ctc_list in cr.dictfetchall():
                     ctc_id = ctc_list.pop('id')
                     res[ctc_id].update(ctc_list)
        return res
    
    _columns = {
                'contract_id':fields.many2one('kdvn.contract.client',"Contract"),
                
                'contracted_idr':fields.float('T.C.A. (IDR)'),
                'claimed_idr':fields.float('Claimed (IDR)'),
                'received_idr':fields.float('Received (IDR)'),
                
                'contracted_usd':fields.float('T.C.A. (USD)'),
                'claimed_usd':fields.float('Claimed (USD)'),
                'received_usd':fields.float('Received (USD)'),
                
                'contracted_jpy':fields.float('T.C.A. (JPY)'),
                'claimed_jpy':fields.float('Claimed (JPY)'),
                'received_jpy':fields.float('Received (JPY)'),
                
                'contracted_sgd':fields.float('T.C.A. (SGD)'),
                'claimed_sgd':fields.float('Claimed (SGD)'),
                'received_sgd':fields.float('Received (SGD)'),
                
                'contracted_eur':fields.float('T.C.A. (EUR)'),
                'claimed_eur':fields.float('Claimed (EUR)'),
                'received_eur':fields.float('Received (EUR)'),
                
                
#                'contracted_idr':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='T.C.A. (IDR)'),
#                'claimed_idr':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='Claimed (IDR)'),
#                'received_idr':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='Received (IDR)'),
#                
#                'contracted_usd':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='T.C.A. (USD)'),
#                'claimed_usd':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='Claimed (USD)'),
#                'received_usd':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='Received (USD)'),
#                
#                'contracted_jpy':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='T.C.A. (JPY)'),
#                'claimed_jpy':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='Claimed (JPY)'),
#                'received_jpy':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='Received (JPY)'),
#                
#                'contracted_sgd':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='T.C.A. (SGD)'),
#                'claimed_sgd':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='Claimed (SGD)'),
#                'received_sgd':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='Received (SGD)'),
#                
#                'contracted_eur':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='T.C.A. (EUR)'),
#                'claimed_eur':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='Claimed (EUR)'),
#                'received_eur':fields.function(_get_contract_summary_amount,type='float',method=True,multi='_get_summary_amount',string='Received (EUR)'),
                
                }
    
    def init(self,cr):
        cr.execute("""Create or replace view kderp_contract_amount_to_cols as Select 
                                vwcontract.id,
                                vwcontract.id as contract_id,
                                sum(case when kc.code='IDR' then contracted_amount else 0 end) as contracted_idr,
                                sum(case when kc.code='IDR' then claimed_amount else 0 end) as claimed_idr,
                                sum(case when kc.code='IDR' then received_amount else 0 end) as received_idr,
                            
                                sum(case when kc.code='USD' then contracted_amount else 0 end) as contracted_usd,
                                sum(case when kc.code='USD' then claimed_amount else 0 end) as claimed_usd,
                                sum(case when kc.code='USD' then received_amount else 0 end) as received_usd,
                            
                                sum(case when kc.code='JPY' then contracted_amount else 0 end) as contracted_jpy,
                                sum(case when kc.code='JPY' then claimed_amount else 0 end) as claimed_jpy,
                                sum(case when kc.code='JPY' then received_amount else 0 end) as received_jpy,
                            
                                sum(case when kc.code='EUR' then contracted_amount else 0 end) as contracted_eur,
                                sum(case when kc.code='EUR' then claimed_amount else 0 end) as claimed_eur,
                                sum(case when kc.code='EUR' then received_amount else 0 end) as received_eur,
                            
                                sum(case when kc.code='SGD' then contracted_amount else 0 end) as contracted_sgd,
                                sum(case when kc.code='SGD' then claimed_amount else 0 end) as claimed_sgd,
                                sum(case when kc.code='SGD' then received_amount else 0 end) as received_sgd    
                            from 
                                (Select 
                                    kcc.id,
                                    kccl.currency_id,
                                    sum(coalesce(vjfc.amount,0)) as contracted_amount,
                                    coalesce(claimed_subtotal,0) as claimed_amount,
                                    coalesce(received_amount,0) as received_amount
                                from
                                    kdvn_contract_client kcc
                                left join
                                    kderp_contract_currency_line  kccl on kcc.id = kccl.contract_id
                                left join
                                    vwquotation_job_for_contract vjfc on kcc.id = vjfc.contract_id and kccl.currency_id = vjfc.currency_id
                                left join --Claimed Amount follow currency
                                    (Select 
                                        contract_id,
                                        kpfc.currency_id,
                                        sum(kpfcd.amount) as claimed_subtotal
                                    from 
                                        kdvn_payment_from_client kpfc
                                    left join
                                        kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                                    where
                                        state not in ('draft','cancel')
                                    group by
                                        kpfc.currency_id,
                                        contract_id) vwclaimed on kcc.id = vwclaimed.contract_id and kccl.currency_id = vwclaimed.currency_id
                                left join --Received Amount in Company Currency
                                    (Select 
                                        contract_id,
                                        kpfc.currency_id,
                                        sum(kpfcd.amount) as received_amount
                                    from
                                        kdvn_payment_from_client kpfc
                                    left join
                                        kderp_payment_from_client_detail kpfcd on kpfc.id = kpfcd.payment_id
                                    left join
                                        (Select distinct payment_id from kdvn_received kr) kr on kpfc.id = kr.payment_id
                                    where
                                        state not in ('draft','cancel') and
                                        coalesce(kr.payment_id,0)>0 
                                    group by
                                        kpfc.currency_id,
                                        contract_id) vwreceived on kcc.id = vwreceived.contract_id and kccl.currency_id = vwreceived.currency_id
                                group by
                                    kcc.id,
                                    kccl.currency_id,
                                    claimed_amount,
                                    received_amount) vwcontract
                            left join
                                kderp_currency kc on vwcontract.currency_id = kc.id
                            group by
                                vwcontract.id""")
kderp_contract_amount_to_cols()